package com.scalar.chiptrack.operations.process.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.LinkedHashMap;
import java.util.Vector;
import java.awt.*;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;

import com.scalar.chiptrack.operations.process.ParameterTypes;
import com.scalar.chiptrack.operations.process.ProcessTypes;
import com.scalar.chiptrack.operations.process.SortProcess;
import com.scalar.chiptrack.operations.process.VendorLocation;
import com.scalar.chiptrack.operations.LotView;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;

public class WorkflowDBManager{

	//protected String baseProductName;
    public String errors;
	public WorkflowDBManager( ){}

	public void saveActivityCollection(DBTransaction transaction , ArrayList activityCollection, String productName ) throws ScalarException
	{
		Iterator it = activityCollection.iterator();
		String baseProductID = null;
		try
		{
			baseProductID = getProductId( transaction, productName );
		 	 //baseProductID =  ProductDao.getProductId( transaction, productName );
			while(it.hasNext())
			{
				HashMap map = (HashMap)it.next();
                String processStepId = (String)map.get("processStepId");
                if ( processStepId == null || processStepId.trim().length() == 0 ) //saving only unSaved processes as processes are saving in Popup panel
                {
                    processStepId = saveActivityInDB( transaction, map, baseProductID );
				    map.put( "processStepId" , processStepId );
                }
            }
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ScalarException("Could not save the Activities ", e);
		}
	}

    public String saveActivityInDB(DBTransaction transaction, Map map, String baseProductID) throws ScalarException
    {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean success = false;

		String processType = null;
		String processName = null;
		String processStepId = null;
		String partNumber = null;
		String comments = "";
		String mktPartNumber = null;
		String isApproved = "1";
		String isPrimaryFGProduct = "1";
		String goodBins = "0";
		String rejectedBins = "0";
		boolean binsRequired = false;
        String partStatus = "Prod";
        String productGroup = null;

        ArrayList vendorLocationList = new ArrayList();

		if( map.containsKey("Type") )
			processType = (String)map.get("Type");

		if( map.containsKey("value") )
			processName = (String)map.get("value");

		if( map.containsKey("processStepId") )
			processStepId = (String)map.get("processStepId");

		if( map.containsKey("partnumber") )
			partNumber = (String)map.get("partnumber");

		if( map.containsKey("comments") )
			comments = (String)map.get("comments");

		if( map.containsKey("mktPartNumber") )
        {
            mktPartNumber = (String)map.get("mktPartNumber");
            if ( mktPartNumber != null && mktPartNumber.trim().length() == 0 )
            {
                mktPartNumber = null;
            }
        }
        
        if( map.containsKey("isApprovedProduct") ){
			isApproved = ((String)map.get("isApprovedProduct")).equalsIgnoreCase("true") ? "0" : "1";
		}
        if( map.containsKey("isPrimaryFGProduct") ){
			isPrimaryFGProduct = ((String)map.get("isPrimaryFGProduct")).equalsIgnoreCase("true") ? "0" : "1";
		}

        if( map.containsKey("goodBinNumber") ){
			goodBins = (String)map.get("goodBinNumber");
		}

		if( map.containsKey("rejectedBinNumber") ){
			rejectedBins = (String)map.get("rejectedBinNumber");
		}

		if( map.containsKey("binsRequired") ){
			if( ((String)map.get("binsRequired")).equals("true") )
				binsRequired = true;
		}

        if( map.containsKey("partStatus") ){
			partStatus = (String)map.get("partStatus");
		}
        if( map.containsKey("productGroup") ){
			productGroup = (String)map.get("productGroup");
		}

        if( processType == null || processType.length() < 1  || partNumber == null || partNumber.length() < 1 || processName == null || processName.length() < 1 )
		{
			return "";
		}

		try
		{
            if(processStepId == null){
				processStepId = "";
			}

            //check for duplicate part number
            int dbProcessStepId = NewProductDAO.getProcessStepId ( transaction, partNumber, processName );
            int psId = 0;
            try
            {
                psId = Integer.parseInt( processStepId );
            } catch ( NumberFormatException ignore ) {}

            if ( dbProcessStepId > 0 && dbProcessStepId != psId )
            {
                errors += "Part Number '" + partNumber + "/" + processName + "' already exists";
                throw new ScalarException ( "Part Number '" + partNumber + "/" + processName + "' already exists" );
            }
            if((processStepId != null) && processStepId.equals(""))
			{
				try
				{
					if(transaction == null){
						conn = DataSource.getConnection();
					}else{
						conn = transaction.getConnection();
					}
					// Insert into Process_Step tableoperations.process.processStep.insertRecord
					ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.processStep.insertRecord") );
					//ps = conn.prepareStatement("insert into Process_Step(PS_Type, PS_Type_Name, PS_Comments,Timestamp )values(?, ?, ?, CURRENT_TIMESTAMP)");
					ps.setString(1, processName);
					ps.setString(2, processType);
					ps.setString(3, comments);

					ps.executeUpdate();
					ps.close();
					ps = null;
					ps = conn.prepareStatement("select @@IDENTITY");
					rs = ps.executeQuery();
					while(rs.next())
					{
						processStepId = rs.getString(1);
					}
					rs.close();
					ps.close();
					rs = null;
					ps = null;

					// Insert into Product Table
					ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.product.insertNewProduct") );
					//ps = conn.prepareStatement("insert into Product(Part_Number, ProcessStep_ID, P_Parent_Product_ID,P_Marketing_Part_Number,P_Comments,TimeStamp,Is_Approved )values(?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?)");
					ps.setString(1, partNumber);
					ps.setInt(2, Integer.parseInt( processStepId ) );
					ps.setInt(3, Integer.parseInt( baseProductID ) );
					ps.setString(4, mktPartNumber);
					ps.setString(5, comments);
					ps.setString(6, isApproved );
                    ps.setString(7, partStatus);
                    ps.setString(8, productGroup);
                    ps.setString(9, isPrimaryFGProduct);
                    ps.executeUpdate();
					success = true;
				}
				catch(Exception e)
				{
					//e.printStackTrace();
					throw new ScalarException("Could not save the Process Step data ", e);
				}
				finally
				{
					if (transaction == null) {
						try {
							if (conn != null) {
								 if (success == true) {
									conn.commit();
								 }
								 else {
									conn.rollback();
								}
							}
						}
						catch (SQLException ignore) {}
					}
					if(rs != null){
						try{ rs.close(); rs=null; }catch(SQLException ignore){}
					}
					if(ps != null){
						try{ ps.close(); ps=null;}catch(SQLException ignore){}
					}
					if( conn != null){
						try{conn.close(); conn=null;}catch(SQLException ignore){}
					}
				}

				//Insert Vendors Information
				if( map.containsKey("vendors"))
				{
					vendorLocationList = getVendorsData( transaction, (String)map.get("vendors"), processType );

					if( vendorLocationList != null && vendorLocationList.size() > 0 )
					{
						VendorLocationDAO.saveVendorLocations( transaction , Integer.parseInt(processStepId) , vendorLocationList.iterator());

						//	save Duplicate part Numbers
						if( map.containsKey("dupPartNumbers"))
						{
							String dupNames = (String )map.get( "dupPartNumbers" );
							if( dupNames != null && dupNames.length() > 0 )
							{
								ArrayList tempList = parseString( dupNames , ';' );
								if( tempList != null && tempList.size() > 0 )
								{
									Object[] objArr = tempList.toArray();
									String[] strArr = new String[objArr.length];
									for (int i=0 ; i < objArr.length ; i++)	{
										strArr[i] = (String)objArr[i];
									}
									ProductDao.saveProductMap( transaction , Integer.parseInt(processStepId) , strArr , vendorLocationList );
								}
							}
						}
					}
				}

				// Insert Process Specific data into Process Parameter table
				saveParameters(transaction , (String)map.get("parameters"), processStepId );
                //Commeted As Process Specific Tables are removed
				//insertProcessData( transaction, Integer.parseInt(processStepId), processType, (String)map.get("parameters"), false, partNumber, mktPartNumber );

				// If Process is Sort insert data into Wip_Step_Bin_Tabel
				if( processType.equals( ProcessTypes.sort) || processType.equals( ProcessTypes.test ) )
				{
					if( binsRequired )
					{
						saveBins( transaction, processStepId, goodBins, rejectedBins, baseProductID );
					}
					else
					{
						deleteBins( transaction, processStepId );
					}
				}
			}
			else // if there is process Step ID
			{
				try
				{
					if(transaction == null){
						conn = DataSource.getConnection();
					}else{
						conn = transaction.getConnection();
					}

					//ps = conn.prepareStatement("update Process_Step set PS_Type = ?, PS_Type_Name=? ,PS_Comments=?, Timestamp= CURRENT_TIMESTAMP  where ProcessStep_ID = ?");
					ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.processStep.updateProcessStep"));

					ps.setString(1, processName);
					ps.setString(2, processType);
					ps.setString(3, comments);
					ps.setString(4, processStepId);
					ps.executeUpdate();
					ps.close();
					ps = null;

                    String dbPartNumber = null;
                    String dbMrktPartNumber = null;
                    String dbComments = null;
                    String dbApprovedFlag =null;
                    String dbPartStatus = null;
                    String dbProductGroup = null;
                    String dbPrimaryFg = null;
                    ps = conn.prepareStatement("Select Part_Number, P_Marketing_Part_Number, P_Comments, Is_Approved, Part_Status, Product_Group, Is_Primary_FG from Product where ProcessStep_ID =? AND P_Parent_Product_id = ? ");
                    ps.setString(1, processStepId);
                    ps.setString(2, baseProductID);

                    rs = ps.executeQuery();
                    if ( rs.next() )
                    {
                        dbPartNumber     = rs.getString("Part_Number") == null ? "" : rs.getString("Part_Number") ;
                        dbMrktPartNumber = rs.getString("P_Marketing_Part_Number") == null ? "" : rs.getString("P_Marketing_Part_Number");
                        dbComments       = rs.getString("P_Comments") == null ? "" : rs.getString("P_Comments");
                        dbPartStatus     = rs.getString("Part_Status");
                        dbProductGroup   = rs.getString("Product_Group");
                        dbApprovedFlag   = rs.getString("Is_Approved");
                        dbPrimaryFg      = rs.getString("Is_Primary_FG");
                    }
                    rs.close();
                    rs = null;
                    ps.close();
                    ps = null;

                    if ( !dbPartNumber.equalsIgnoreCase( partNumber ) || !dbMrktPartNumber.equalsIgnoreCase( mktPartNumber ) || !dbComments.equalsIgnoreCase( comments) ||
                         !dbPartStatus.equalsIgnoreCase( partStatus ) || !dbProductGroup.equalsIgnoreCase( productGroup ) || !dbApprovedFlag.equalsIgnoreCase(isApproved) ||
                          !dbPrimaryFg.equalsIgnoreCase( isPrimaryFGProduct )  )
                    {                           
                        ps = conn.prepareStatement("INSERT INTO PRODUCT_HISTORY Select Product_id,Part_Number, P_Parent_Product_id, P_Marketing_Part_Number, P_Comments, Is_Approved, Part_Status, Product_Group, Is_Primary_FG, 1,1,'',Current_Timestamp from Product where ProcessStep_ID =? AND P_Parent_Product_id = ? ");
                        ps.setString(1, processStepId);
                        ps.setString(2, baseProductID);

                        ps.executeUpdate();
                        ps.close();
                        ps = null;
                    }
					// ps = conn.prepareStatement("Update Product set Part_Number =?, p_comments = ?, P_Marketing_Part_Number= ?, Is_Approved = ?, PART_STATUS = ? where ProcessStep_ID =? and P_Parent_Product_ID = ?");
					ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.product.updateProcessStepTable"));
					ps.setString(1, partNumber);
					ps.setString(2, comments);
					ps.setString(3, mktPartNumber);
					ps.setString(4, isApproved);
                    ps.setString(5, partStatus);
                    ps.setString(6, productGroup);
                    ps.setString(7, isPrimaryFGProduct);
                    ps.setString(8, processStepId);
					ps.setInt(9, Integer.parseInt( baseProductID));
					ps.executeUpdate();
					success = true;
				}
				catch(Exception e)
				{
					//e.printStackTrace();
					throw new ScalarException("Could not save the Process Step data ", e);
				}
				finally
				{
					if (transaction == null) {
						try {
							if (conn != null) {
								 if (success == true) {
									conn.commit();
								 }
								 else {
									conn.rollback();
								}
							}
						}
						catch (SQLException ignore) {}
					}

					if(rs != null){
						try{ rs.close(); rs=null;}catch(SQLException ignore){}
					}
					if(ps != null){
						try{ ps.close(); ps=null;}catch(SQLException ignore){}
					}
					if( conn != null){
						try{conn.close(); conn=null;}catch(SQLException ignore){}
					}
				}

				//	update Vendors Information
				if( map.containsKey("vendors"))
				{
					vendorLocationList = getVendorsData( transaction, (String)map.get("vendors"), processType );

					if( vendorLocationList != null && vendorLocationList.size() > 0 )
					{
						VendorLocationDAO.deleteVendorLocations( transaction , Integer.parseInt(processStepId) );
						VendorLocationDAO.saveVendorLocations( transaction , Integer.parseInt(processStepId) , vendorLocationList.iterator());

						//	save Duplicate part Numbers
                        String[] strArr = null;
                        if( map.containsKey("dupPartNumbers"))
						{
							String dupNames = (String )map.get( "dupPartNumbers" );
							if( dupNames != null && dupNames.length() > 0 )
							{
								ArrayList tempList = parseString( dupNames , ';' );
								if( tempList != null && tempList.size() > 0 )
								{
									Object[] objArr = tempList.toArray();
									strArr = new String[objArr.length];
									for (int i=0 ; i < objArr.length ; i++)
									{
										strArr[i] = (String)objArr[i];
									}
								}
							}
						}
                        if ( strArr == null )
                        {
                            strArr = new String[1];
                            strArr[0] = "";
                        }
                        ProductDao.saveProductMap( transaction , Integer.parseInt(processStepId) , strArr , vendorLocationList );
                    }
				}

				// update  Process Specific data
                //Commeted As Process Specific Tables are removed
				//insertProcessData( transaction, Integer.parseInt(processStepId), processType, (String)map.get("parameters"), true, partNumber, mktPartNumber);
				// Insert Process Specific data into Process Parameter table
				saveParameters( transaction , (String)map.get("parameters"), processStepId );

				// If Process is Sort insert data into Wip_Step_Bin_Tabel

				if( processType.equals( ProcessTypes.sort)  || processType.equals( ProcessTypes.test ) )
				{
					if( binsRequired ) {
						deleteBins( transaction, processStepId );
						saveBins( transaction, processStepId, goodBins, rejectedBins, baseProductID );
					}
					else {
						deleteBins( transaction, processStepId );
					}
				}
			}
			success = true;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ScalarException("Could not save the Process Step data ", e);
		}
		finally{
			if (transaction == null) {
                try {
                    if (conn != null) {
                         if (success == true) {
                         	conn.commit();
                         }
                         else {
                        	conn.rollback();
                    	}
                	}
                }
            	catch (SQLException ignore) {}
            }
			if(rs != null){
				try{ rs.close(); }catch(SQLException ignore){}
			}
			if(ps != null){
				try{ ps.close(); }catch(SQLException ignore){}
			}
			if( conn != null){
				try{conn.close(); }catch(SQLException ignore){}
			}
		}
		return processStepId;
	}

    private void deleteBins( DBTransaction transaction, String processStepId ) throws ScalarException
    {
		Connection conn = null;
		PreparedStatement ps = null;
		try
		{
			if( transaction == null ){
				conn = DataSource.getConnection();
			}else{
				conn = transaction.getConnection();
			}
			ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.binsetup.deleteBinsByProcessStepId") );
			//ps = conn.prepareStatement("delete from  wip_Step_Bin_Table where ProcessStep_ID = ? ");
			ps.setInt( 1, Integer.parseInt( processStepId ));
			ps.executeUpdate();
		}
		catch (SQLException e){
			e.printStackTrace();
			throw new ScalarException ("Could not delete the sort Wip Step data ", e  );
		}
		finally
		{
			if (transaction == null){
				try{
					if (conn != null){
						conn.commit();
					}
				}catch (SQLException ignore) {}
			}
			if(ps != null){
				try{ ps.close(); }catch(SQLException se){se.printStackTrace();}
			}
			if(conn != null){
				try{   conn.close(); }catch(SQLException se){se.printStackTrace();}
			}
		}
    }


    private void saveBins(DBTransaction transaction, String processStepId, String goodBins, String rejectedBins, String baseProductID) throws ScalarException
    {
    	int goodBinNumber = 1;
		int rejectedBinNumber = 1;
		int index = 1;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

    	try{
			goodBinNumber = Integer.parseInt( goodBins );
    	}catch(Exception e){
			goodBinNumber = 1;
    	}
		try{
			rejectedBinNumber = Integer.parseInt( rejectedBins );
		}catch(Exception e){
			rejectedBinNumber = 1;
		}

		try
		{
			if( transaction == null ){
				conn = DataSource.getConnection();
			}else{
				conn = transaction.getConnection();
			}

			ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.binsetup.insertSortTestBins"));
			//ps = conn.prepareStatement("insert into wip_Step_Bin_Table ( ProcessStep_ID,WSBT_Bin_Number,WSBT_Bin_Type ) values(?,?,? )");
			for ( int i = 0 ; i < goodBinNumber ; i++ ){
				ps.setInt( 1, Integer.parseInt( processStepId ));
				ps.setString( 2, "Bin"+index++ );
				ps.setString( 3, "Accept" );
				//ps.setInt( 4, Integer.parseInt( baseProductID ));
				ps.addBatch();
			}

			for ( int j = 0 ; j < rejectedBinNumber ; j++ ){
				ps.setInt( 1, Integer.parseInt( processStepId ));
				ps.setString( 2, "Bin"+index++ );
				ps.setString( 3, "N/A" );
				//ps.setInt( 4, Integer.parseInt( baseProductID ) );
				ps.addBatch();
			}
			ps.executeBatch();
		}
		catch (SQLException e){
			e.printStackTrace();
			throw new ScalarException ("Could not save the sort Wip Step data ", e  );
		}
		finally
		{
			if (transaction == null){
				try{
					if (conn != null){
						conn.commit();
					}
				}catch (SQLException ignore) {}
			}
			if(ps != null){
				try{ ps.close(); }catch(SQLException se){se.printStackTrace();}
			}
			if(conn != null){
				try{   conn.close(); }catch(SQLException se){se.printStackTrace();}
			}
		}
    }

    private HashMap getParameterMap( String parameterString )
    {
    	HashMap parameterMap = new HashMap();
    	if( parameterString != null && parameterString.length() > 0 )
    	{
    		Iterator it = parseString( parameterString , ';').iterator();

			while(it.hasNext())
			{
				ArrayList tempList = parseString((String)it.next(), '=');
				if(!tempList.isEmpty() && tempList.size() == 2)
				{
					parameterMap.put(tempList.get(0),tempList.get(1));
				}
				else if(!tempList.isEmpty() && tempList.size() == 3)
				{
					parameterMap.put(tempList.get(0),tempList.get(2));
				}
			}
    	}

    	return parameterMap;
    }

    private ArrayList getVendorsData( DBTransaction transaction, String vendors, String processType) throws ScalarException
    {
		ArrayList vendorLocationList = new ArrayList();
		VendorLocation vendorLoc = null;
		ArrayList tempList = parseString( vendors , '#' );
		Iterator it = tempList.iterator();
        errors = "";

		while( it.hasNext() )
		{
			ArrayList vendorDetail  = parseString((String)it.next(),';');
			Iterator vendorIt = vendorDetail.iterator();
			vendorLoc = new VendorLocation();
			int i = 1;

			while( vendorIt.hasNext() )
			{
                String itrValue = (String)vendorIt.next();
                switch (i)
				{
					case 1:
						vendorLoc.setSelect(itrValue);
						break;
					case 2:
						String priority = itrValue;
						if( priority == null ){
							vendorLoc.setPriority("0");
						}else{
							try	{
								Integer.parseInt( priority );
								vendorLoc.setPriority( priority );
							}catch(NumberFormatException e ){
								vendorLoc.setPriority("0");
							}
						}
						break;
					case 3:
						vendorLoc.setLocationName(itrValue);
						break;
					case 4:
						String split = itrValue;
						if( split == null )	{
							vendorLoc.setSplit("0.0");
						}else{
							try	{
								Float.parseFloat( split );
								vendorLoc.setSplit( split );
							}catch(NumberFormatException e ){
								vendorLoc.setSplit("0.0");
							}
						}
						break;
					case 5:
						String capacityWeek = itrValue;
						if( capacityWeek == null ){
							vendorLoc.setCapacityWeek("0");
						}else{
							try {
								Integer.parseInt( capacityWeek );
								vendorLoc.setCapacityWeek( capacityWeek );
							}catch(NumberFormatException e ){
								vendorLoc.setCapacityWeek("0");
							}
						}
						break;
/*					case 6:
						try	{
							vendorLoc.setNormalCost( Float.parseFloat( itrValue ) );
						}catch(NumberFormatException e ){
							vendorLoc.setNormalCost( 0.0f );
						}
						catch( NullPointerException e )	{
							vendorLoc.setNormalCost( 0.0f );
						}
						break;
					case 7:
						try	{
							vendorLoc.setExpressCost( Float.parseFloat( itrValue ));
						}catch(NumberFormatException e ){
							vendorLoc.setExpressCost( 0.0f );
						}catch(NullPointerException e ){
							vendorLoc.setExpressCost( 0.0f );
						}
						break;  */
					case 6:
						String nCycleTime = itrValue;
						if( nCycleTime == null ){
							vendorLoc.setNormalCycleTime("0");
						}else{
							try	{
								Integer.parseInt( nCycleTime );
								vendorLoc.setNormalCycleTime( nCycleTime );
							}catch(NumberFormatException e ){
								vendorLoc.setNormalCycleTime("0");
							}
						}
						break;
					case 7:
						String eCycleTime = itrValue;
						if( eCycleTime == null ){
							vendorLoc.setExpressCycleTime("0");
						}else{
							try{
								Integer.parseInt( eCycleTime );
								vendorLoc.setExpressCycleTime( eCycleTime );
							}catch(NumberFormatException e ){
								vendorLoc.setExpressCycleTime("0");
							}
						}
						break;
/*					case 10:
						try {
							vendorLoc.setExpectedYield( Float.parseFloat( itrValue ));
						}catch(NumberFormatException e ){
							vendorLoc.setExpectedYield( 0.0f );
						}catch(NullPointerException e){
							vendorLoc.setExpectedYield( 0.0f );
						}
						break; */
				}
				i++;
			}

			if( vendorLoc.getSelect().equalsIgnoreCase("true") )
			{
				if( processType.equals(ProcessTypes.markAndPack) ) {
					processType = "Mark &amp; Pack";
				}

				vendorLoc.setLocationID( getLocationIDForVendor( transaction, vendorLoc.getLocationName().trim(), processType ));
                
				if( !vendorLoc.getLocationID().equals("-1") && vendorLoc.getLocationID().length() > 0  )
				{
					vendorLocationList.add( vendorLoc );
				}
                else
                {
                    errors = errors + " ="  + vendorLoc.getLocationName() +"=" + processType;
                }
			}
		}

        if ( errors.length() > 0 )
        {
            errors = "Vendor(s) " + errors + " not exists";
            throw new ScalarException ();
        }
        return vendorLocationList;
    }

    private String getLocationIDForVendor(DBTransaction transaction, String location , String processType) throws ScalarException
    {
        String locationID = "-1";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try
        {
        	if( transaction == null ) {
        		conn = DataSource.getConnection();
        	}
        	else {
				conn = transaction.getConnection();
        	}

			ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.vendorLocation.inventoryLocationsForProcessType"));
			//ps = conn.prepareStatement("select Location_ID from Location, Address  where L_Type like ? and L_Is_Deleted = 'N' and Location.Address_ID = Address.Address_id and (L_Vendor_Name + '/' + City) like ? ");
			ps.setString( 1 , processType );
			ps.setString( 2 , location );
			rs = ps.executeQuery();

			if(rs.next()){
				locationID = rs.getString("Location_ID");
			}
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new ScalarException("Could not get the Vendor Location" , e );
        }
		finally
		{
			if( rs != null ){
				try{ rs.close(); }catch(SQLException se){}
			}
			if(ps != null){
				try{ ps.close(); }catch(SQLException se){}
			}
			if(conn != null){
				try{   conn.close(); }catch(SQLException se){}
			}
		}
        return locationID;
    }

    public void saveParameters(DBTransaction transaction, String parameters, String processStepId) throws ScalarException{
		Connection conn = null;
		PreparedStatement ps = null;

		try
		{
			if(transaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = transaction.getConnection();
			}
			ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.deleteProcessParameters") );
			//ps = conn.prepareStatement("delete from Process_Parameter where ProcessStep_ID = ?");
			ps.setString( 1 , processStepId );
			ps.executeUpdate();
			ps.close();
			ps = null;
			if( parameters != null )
			{
				Iterator it = parseString( parameters, ';' ).iterator();
				//ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.insertProcessParameters"));
				ps = conn.prepareStatement("insert into Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value)values(?, ?, ?, ?)");
				while( it.hasNext() )
				{
					ArrayList tempList = parseString( (String)it.next(), '=' );

					if( !tempList.isEmpty() && tempList.size() > 2 )
					{
						ps.setString( 1, processStepId );
						ps.setString( 2, (String)tempList.get(0) );
						ps.setString( 3, (String)tempList.get(1) );
						ps.setString( 4, (String)tempList.get(2) );
						ps.addBatch();
					}
					else if ( !tempList.isEmpty() && tempList.size() == 2 )
					{
						ps.setString( 1, processStepId );
						ps.setString( 2, (String)tempList.get(0) );
						ps.setString( 3, "" );
						ps.setString( 4, (String)tempList.get(1) );
						ps.addBatch();
					}
				}
				ps.executeBatch();
			}
		}catch(Exception e){
			e.printStackTrace();
			throw new ScalarException("Could not save the Process Step data ", e);
		}finally{
			if (transaction == null){
				try {
					if (conn != null){
						conn.commit();
					}
				}catch (SQLException ignore) {}
			}
			if(ps != null){
				try{ ps.close(); }catch(Exception e){e.printStackTrace();}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){e.printStackTrace();}
			}
		}
	}

    public static HashMap getAllVendorLocations()
    {
       	HashMap vendorMap = new HashMap();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList processList = ProcessTypes.newInstance().getList();
		ArrayList vendorList = null;

		try
		{
			conn = DataSource.getConnection();
			ps = conn.prepareStatement( SqlQueryMappings.get("vendor.getProcessLocations"));
			//ps = conn.prepareStatement("select Location_ID, L_Vendor_Name, City, Vendor_id from Location, Address  where L_Type like ? and L_Is_Deleted = 'N' and Location.Address_ID = Address.Address_id order by L_Vendor_Name");
			if( processList != null && processList.size() > 0 )
			{
				Iterator it = processList.iterator();
				while( it.hasNext() )
				{
					String process = (String)it.next();

					if( process.equals( ProcessTypes.markAndPack ) )
					{
						ps.setString( 1 , "Mark &amp; Pack" );
					}
					else
					{
						ps.setString( 1 , process );
					}

					rs = ps.executeQuery();
					vendorList = new ArrayList();
					while( rs.next() )
					{
						vendorList.add( rs.getString("L_Vendor_Name") + "/" + rs.getString("City") );
					}

					vendorMap.put( process , vendorList );
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(ps != null){
				try{ ps.close(); }catch(SQLException ignore){}
			}
			if( conn != null){
				try{ conn.close(); }catch(SQLException ignore){}
			}
		}
        return vendorMap;
    }


	public static void storeInventoryData (DBTransaction dbTransaction, int processStepId, int minLevel, int maxLevel, int maxTime  ) throws ScalarException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try {
			if (dbTransaction == null) {
				conn = DataSource.getConnection();
			}
			else {
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement ( SqlQueryMappings.get ("opearations.waferStore.insertNew"));
			//pstmt = conn.prepareStatement ("INSERT INTO Inventory_Step_Table (ProcessStep_ID, IST_Min_Level, IST_Max_Level, IST_Max_Time, Timestamp) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)");

			pstmt.setInt (1, processStepId);
			pstmt.setInt (2, minLevel );
			pstmt.setInt (3, maxLevel);
			pstmt.setInt (4, maxTime );

			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			success = true;
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could not store Inventory data", sqle);
		}
		finally {
			if (dbTransaction == null) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}

	private void updateInventoryData(DBTransaction transaction, int processStepId, int minLevel, int maxLevel, int maxTime) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try {
			if ( transaction == null) {
				conn = DataSource.getConnection();
			}
			else {
				conn = transaction.getConnection();
			}
			pstmt = conn.prepareStatement ( SqlQueryMappings.get("operations.process.updateInventoryStepTable") );
			//pstmt = conn.prepareStatement ("update Inventory_Step_Table set IST_Min_Level=?, IST_Max_Level=?, IST_Max_Time=?, Timestamp = CURRENT_TIMESTAMP where ProcessStep_ID = ? ");

			pstmt.setInt (1, minLevel );
			pstmt.setInt (2, maxLevel);
			pstmt.setInt (3, maxTime );
			pstmt.setInt (4, processStepId);

			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			success = true;
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could not update Inventory data", sqle);
		}
		finally {
			if (transaction == null) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}


	public static void storeFinalInventoryData (DBTransaction dbTransaction, int processStepId, int minLevel, int maxLevel, int reOrderLevel , String partNumber, String mktPartNumber  ) throws ScalarException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try {
			if (dbTransaction == null) {
				conn = DataSource.getConnection();
			}
			else {
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement ( SqlQueryMappings.get ("opearations.finishedgoods.insertNew"));

			pstmt.setInt (1, processStepId);
			pstmt.setInt (2, minLevel );
			pstmt.setInt (3, maxLevel);
			pstmt.setInt (4, reOrderLevel );
			pstmt.setString (5, partNumber );
			pstmt.setString (6, mktPartNumber );

			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			success = true;
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could not store Final Inventory data", sqle);
		}
		finally {
			if (dbTransaction == null) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}

	private void updateFinalInventoryData(DBTransaction transaction, int processStepId, int minLevel, int maxLevel, int reOrderLevel, String partNumber, String mktPartNumber) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try {
			if ( transaction == null) {
				conn = DataSource.getConnection();
			}
			else {
				conn = transaction.getConnection();
			}
			//pstmt = conn.prepareStatement ("update Inventory_Step_Table set IST_Min_Level=?, IST_Max_Level=?, IST_Max_Time=?, Timestamp = CURRENT_TIMESTAMP where ProcessStep_ID = ? ");
			pstmt = conn.prepareStatement (SqlQueryMappings.get("opearations.finishedgoods.update"));
			pstmt.setInt (1, minLevel );
			pstmt.setInt (2, maxLevel);
			pstmt.setInt (3, reOrderLevel );
			pstmt.setString (4, partNumber );
			pstmt.setString (5, mktPartNumber );
			pstmt.setInt (6, processStepId );

			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

			success = true;
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could not update Inventory data", sqle);
		}
		finally {
			if (transaction == null) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}


	public boolean saveTransitionCollection( DBTransaction transaction , ArrayList transitionCollection , HashMap IdProcessMap , String productName ) throws ScalarException
	{
		WorkflowDBManager workFlowdb = new WorkflowDBManager();
		Iterator it 				 = transitionCollection.iterator();
		boolean success 			 = false;
		String baseProductID 		 = "0";
		Connection conn 			 = null;
		PreparedStatement pstmt 	 = null;

		try
		{
			if ( transaction == null ) {
				conn = DataSource.getConnection();
			}
			else {
				conn = transaction.getConnection();
			}
			//baseProductID =  ProductDao.getProductId( transaction, productName );
			baseProductID =  getProductId( transaction, productName );

			pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.process.product.deleteNextProcessStepId"));
			//pstmt = conn.prepareStatement("delete from Process_Step_Traverse_Table where Parent_Product_ID = ? ");
			pstmt.setString( 1 , baseProductID );
			pstmt.executeUpdate();
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could delete previous store Process step traverse data", sqle);
		}
		finally {
			if ( transaction == null ) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		try
		{
			while( it.hasNext() )
			{
				HashMap map = (HashMap)it.next();
				if( map.containsKey( "From" ) && map.containsKey( "To" ) )
				{
					String fromId 	= (String)IdProcessMap.get( map.get( "From" ) );
					String toId 	= (String)IdProcessMap.get( map.get( "To" ) );
					String transportLocations 	= null;
					ArrayList transportList 	= new ArrayList();
					ArrayList transportInfo		= new ArrayList();

					if( fromId != null && fromId.length() > 0 && toId != null && toId.length() > 0 )
					{
						workFlowdb.saveTransitionInDB( transaction, fromId, toId, baseProductID );

						// Intentionaly this is kept in a separate try catch block to ignore any
						// Exception statements. This should be removed once the vendorId is set in XPDL file.
						try
						{
							if( map.containsKey( "transportLocations" ) )
							{
								transportLocations 	= (String) map.get( "transportLocations" );
								transportList 		= parseString( transportLocations, '#' );

								if( transportList != null && transportList.isEmpty() == false )
								{
									for ( int i = 0; i < transportList.size(); i++ )
									{
										transportInfo = parseString( (String)transportList.get( i ), ';' );

										if( transportInfo != null && transportInfo.isEmpty() == false && transportInfo.size() > 5 )
										{
											String fromLocation 	= null;
											String fromLocationId 	= null;
											String toLocation   	= (String)transportInfo.get(1);
											String toLocationId 	= null;
											String temp 			= null;

											temp = (String)transportInfo.get(0);

											if( temp.indexOf( '/' ) != -1 )
											{
												fromLocation = ( temp ).substring(0, temp.indexOf('/') );
											}
											else
											{
												fromLocation = temp;
											}

											temp = (String)transportInfo.get(1);

											if( temp.indexOf( '/' ) != -1 )
											{
												toLocation = ( temp ).substring(0, temp.indexOf('/') );
											}
											else
											{
												toLocation = temp;
											}

											fromLocationId = getLocationIDForVendor( transaction, fromLocation, Integer.parseInt( fromId ) );

											toLocationId   = getLocationIDForVendor( transaction, toLocation, Integer.parseInt( toId ) );

											//System.out.println( "From Location ID "+ fromLocationId + " ToLocation Id  "+ toLocationId );

											if( !fromLocationId.equalsIgnoreCase( "-1" ) && !toLocationId.equalsIgnoreCase( "-1" ) )
											{
												saveTransportDetails( transaction, toId, fromLocationId, toLocationId, (String)transportInfo.get(2), (String)transportInfo.get(3), (String)transportInfo.get(4), (String)transportInfo.get(5) );
											}
										}
									}
								}
							}
						}
						catch( Exception e )
						{
							e.printStackTrace();
						}
					}
				}
			}
            saveFinishedGoodsFromFgToFgiMapTable( transaction, baseProductID);
			success = true;
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could delete previous store Process step traverse data", sqle);
		}
		return success;
	}

    public void saveFinishedGoodsFromFgToFgiMapTable( DBTransaction dbTransaction, String baseProductId ) throws ScalarException
    {
        int baseProductIdInt 	= -1;
		Connection conn 	= null;
		PreparedStatement ps= null;
        boolean success = false;
        try
        {
            baseProductIdInt = Integer.parseInt( baseProductId);
        }
        catch( NumberFormatException e)
        {
            e.printStackTrace();
        }
		try
		{
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
			ps = conn.prepareStatement( SqlQueryMappings.get("operations.finishedGoods.saveMappingsInTraverseTable"));
            ps.setInt(1, baseProductIdInt);
			ps.executeUpdate();
            success = true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			throw new ScalarException("Could not save FGI to FG Transitions in Traverse Table " , e );
		}
		finally
		{
            if ( dbTransaction == null ) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (ps != null) {
                try { ps.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
    }


    /**
     * updating the part number comments in PRODUCT table with base_part_number or base_part_number/packagetype
     * depending on process type
     * @param transaction
     * @param baseProductId
     * @throws ScalarException
     */
    public void updatePartNumberComments( DBTransaction transaction, String baseProductId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            if ( transaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = transaction.getConnection();
            }
            cstmt = conn.prepareCall("{call SP_UPDATE_PART_NUMBER_COMMENTS(?)}" );
            cstmt.setInt( 1 , Integer.parseInt( baseProductId ) );
            cstmt.execute();
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save part number comments", sqle);
        }
        finally
        {
            if ( transaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true ) {
                               conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


	public void saveTransportDetails( DBTransaction transaction, String processStepId, String fromId, String toId, String normalTime, String expressTime, String normalCost, String expressCost ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try
		{
			if ( transaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = transaction.getConnection();
			}
			//System.out.println( "Inserting values into Transportation table" );
			pstmt = conn.prepareStatement( "insert into transportation_table values ( ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP )" );
			pstmt.setInt( 1 , Integer.parseInt( processStepId ) );
			pstmt.setInt( 2 , Integer.parseInt( fromId ) );
			pstmt.setInt( 3 , Integer.parseInt( toId ) );
			pstmt.setInt( 4 , Integer.parseInt ( normalTime ) );
			pstmt.setFloat( 5 , Float.parseFloat( normalCost ) );
			pstmt.setInt( 6 , Integer.parseInt ( expressTime ) );
			pstmt.setFloat( 7 , Float.parseFloat ( expressCost ) );

			pstmt.executeUpdate();
			success = true;
		}
		catch ( Exception sqle )
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Transport data", sqle);
		}
		finally
		{
			if ( transaction == null )
			{
				try
				{
					if ( conn != null )
					{
						if ( success == true ) {
							   conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}


    /**
     * @param transaction
     * @param fromLocation
     * @param
    
     * @return
     */
    private String getLocationIDForVendor( DBTransaction transaction, String fromLocation, int processStepId ) throws ScalarException
    {
        String locationID 	= "-1";
		Connection conn 	= null;
		PreparedStatement ps= null;
		ResultSet rs 		= null;

		try
		{
			if( transaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = transaction.getConnection();
			}

			ps = conn.prepareStatement( "select Location_Id from Location, Address where L_Type like ( Select CASE PS_Type_Name WHEN 'Mark & Pack' THEN 'Mark &amp; Pack' ELSE PS_Type_Name END from process_Step  where processStep_Id = ? ) and L_Is_Deleted = 'N' and Location.Address_ID = Address.Address_id and L_Vendor_Name = ? ");
			ps.setInt( 1 , processStepId );
			ps.setString( 2 , fromLocation );
			rs = ps.executeQuery();

			if( rs.next() )
			{
				locationID = rs.getString( "Location_ID" );
			}

		}
		catch (SQLException e)
		{
			e.printStackTrace();
			throw new ScalarException("Could not get the Vendor Location" , e );
		}
		finally
		{
			if( rs != null ){
				try{ rs.close(); }catch(SQLException se){}
			}
			if(ps != null){
				try{ ps.close(); }catch(SQLException se){}
			}
			if(conn != null){
				try{   conn.close(); }catch(SQLException se){}
			}
		}
		return locationID;
    }

    public void saveTransitionInDB(DBTransaction transaction, String fromId, String toId, String baseProductID) throws ScalarException
    {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try {
			if ( transaction == null) {
				conn = DataSource.getConnection();
			}
			else {
				conn = transaction.getConnection();
			}

			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.updateNextProcessStepId"));
			//pstmt = conn.prepareStatement("INSERT INTO Process_Step_Traverse_Table (ProcessStep_ID, Next_ProcessStep_ID) VALUES (?, ?)");
			pstmt.setString( 1 , fromId );
			pstmt.setString( 2 , toId );
			pstmt.setString( 3 , baseProductID);
			pstmt.executeUpdate();

			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement( "Delete from Transportation_Table where ProcessStep_ID = ? " );
			pstmt.setInt( 1, Integer.parseInt( toId ) );
			pstmt.executeUpdate();

			success = true;
		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could not store Process step traverse data", sqle);
		}
		finally {
			if (transaction == null) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
    }

	public static ArrayList parseString( String string, char delim )
	{
		char[] charArray 	= string.toCharArray();
		ArrayList list 		= new ArrayList();
		StringBuffer sb 	= new StringBuffer();

		for( int i = 0; i < charArray.length; i++ )
		{
			if( charArray[i] != delim )
			{
				sb.append( charArray[i] );
			}
			else
			{
				list.add( sb.toString().trim() );
				sb.delete( 0, sb.length() );
			}
		}

		list.add( sb.toString().trim() );

		return list;
	}

	public static String getProductId(DBTransaction dbTransaction, String baseProductName) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		PreparedStatement pstmt = null;
		String productId ="";
		try
		{
			if (dbTransaction == null) {
				conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

			pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.process.getBaseProductIdByPartNumber") );
			//pstmt = conn.prepareStatement("SELECT PRODUCT_ID FROM PRODUCT where Part_Number = ? and P_Parent_Product_ID is null");
			pstmt.setString(1,baseProductName);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				productId = rs.getString(1);
			}
		}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException( "In getting Base Product Id ", sql );
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return productId;
	}

	private void insertProcessData(DBTransaction transaction, int processStepId, String processType, String parameterString , boolean flag  , String partNumber, String mktPartNumber  ) throws ScalarException
	{
		HashMap parameterMap = getParameterMap( parameterString );

		try{
			if( processType.equals( ProcessTypes.fab ) )
			{
				FabDAO fabDao = new FabDAO();
				float lotHoldYeildLimit = 0.0f;
				float standardUnitCost = 0.0f;
				int gross = 0;
				String poFrequency = "";
				float lotScrapYieldLimit = 0.0f;
				float targetYield = 0.0f;
				int procedureDocId = 1;
				int maskId = 1;
				int dieSize = 0;

				if( parameterMap.containsKey( ParameterTypes.lotHoldYeildLimit ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit ) != null) && ((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit )).length() > 0 )
					{
						try{
							lotHoldYeildLimit = Float.parseFloat((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit ));
						}catch( NumberFormatException e){
							lotHoldYeildLimit = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.standardUnitCost ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.standardUnitCost ) != null) && ((String)parameterMap.get( ParameterTypes.standardUnitCost )).length() > 0 )
					{
						try{
							standardUnitCost = Float.parseFloat((String)parameterMap.get( ParameterTypes.standardUnitCost ));
						}catch( NumberFormatException e){
							standardUnitCost = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.grossDieWafer ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.grossDieWafer ) != null) && ((String)parameterMap.get( ParameterTypes.grossDieWafer )).length() > 0 )
					{
						try{
							gross = Integer.parseInt((String)parameterMap.get( ParameterTypes.grossDieWafer ));
						}catch( NumberFormatException e){
							gross = 0;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.lotScarpyeildLimit ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ) != null) && ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit )).length() > 0 ){
						try{
							lotScrapYieldLimit = Float.parseFloat((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ));
						}catch( NumberFormatException e){
							lotScrapYieldLimit = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.targetYeild ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.targetYeild ) != null) && ((String)parameterMap.get( ParameterTypes.targetYeild )).length() > 0 )
					{
						try{
							targetYield = Float.parseFloat((String)parameterMap.get( ParameterTypes.targetYeild ));
						}catch( NumberFormatException e){
							targetYield = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.dieSize ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.dieSize ) != null) && ((String)parameterMap.get( ParameterTypes.dieSize )).length() > 0 )
					{
						try{
							dieSize = Integer.parseInt((String)parameterMap.get( ParameterTypes.dieSize ));
						}catch( NumberFormatException e){
							dieSize = 0;
						}
					}
				}


				if( flag)
				{
					fabDao.updateFab( transaction, processStepId, lotHoldYeildLimit, standardUnitCost, gross, "", lotScrapYieldLimit, targetYield, procedureDocId, maskId, dieSize );
				}
				else
				{
					fabDao.saveFab( transaction, processStepId , lotHoldYeildLimit, standardUnitCost , gross, "", lotScrapYieldLimit, targetYield, procedureDocId, maskId, dieSize);
				}
			}
			else if( processType.equals( ProcessTypes.waferStore ) || processType.equals( ProcessTypes.classStore ) || processType.equals( ProcessTypes.diebank ) )
			{
				int minLevel = 0;
				int maxLevel = 0;
				int maxTime  = 0;

				if( parameterMap.containsKey( ParameterTypes.minLevel ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.minLevel ) != null) && ((String)parameterMap.get( ParameterTypes.minLevel )).length() > 0 ){
						try{
							minLevel = Integer.parseInt((String)parameterMap.get( ParameterTypes.minLevel ));
						}catch( NumberFormatException e){
							minLevel = 0;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.maxLevel ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.maxLevel ) != null) && ((String)parameterMap.get( ParameterTypes.maxLevel )).length() > 0 ){
						try{
							maxLevel = Integer.parseInt((String)parameterMap.get( ParameterTypes.maxLevel ));
						}catch( NumberFormatException e){
							maxLevel = 0;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.maxTime ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.maxTime ) != null) && ((String)parameterMap.get( ParameterTypes.maxTime )).length() > 0 ){
						try{
							maxTime = Integer.parseInt((String)parameterMap.get( ParameterTypes.maxTime ));
						}catch( NumberFormatException e){
							maxTime = 0;
						}
					}
				}

				if( flag )
				{
					updateInventoryData( transaction, processStepId, minLevel, maxLevel, maxTime );
				}
				else
				{
					storeInventoryData ( transaction, processStepId, minLevel, maxLevel, maxTime );
				}
			}
			else if( processType.equals( ProcessTypes.sort ) )
			{
				SortProcess sort = new SortProcess();
				float expectedNetDieWafer =0.0f;
				float lotHoldYeildLimit = 0.0f;
				float lotScrapYieldLimit = 0.0f;
				float standardUnitCost = 0.0f;
				String poFrequency = "";
				int procedureDocId = 1;
				int programDocId = 1;
				String probeCardNo = "";
				String sys = "";
				String rmaCriteria = "";
				String scrapCriteria = "";

				if( parameterMap.containsKey( ParameterTypes.expectedNetDieWafer ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.expectedNetDieWafer ) != null) && ((String)parameterMap.get( ParameterTypes.expectedNetDieWafer )).length() > 0 )
					{
						try{
							//expectedNetDieWafer = Integer.parseInt((String)parameterMap.get( ParameterTypes.expectedNetDieWafer ));
							expectedNetDieWafer = Float.parseFloat((String)parameterMap.get( ParameterTypes.expectedNetDieWafer ));
						}catch( NumberFormatException e){
							expectedNetDieWafer =0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.lotHoldYeildLimit ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit ) != null) && ((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit )).length() > 0 )
					{
						try{
							lotHoldYeildLimit = Float.parseFloat((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit ));
						}catch( NumberFormatException e){
							lotHoldYeildLimit = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.lotScarpyeildLimit ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ) != null) && ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit )).length() > 0 ){
						try{
							lotScrapYieldLimit = Float.parseFloat((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ));
						}catch( NumberFormatException e){
							lotScrapYieldLimit = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.standardUnitCost ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.standardUnitCost ) != null) && ((String)parameterMap.get( ParameterTypes.standardUnitCost )).length() > 0 )
					{
						try{
							standardUnitCost = Float.parseFloat((String)parameterMap.get( ParameterTypes.standardUnitCost ));
						}catch( NumberFormatException e){
							standardUnitCost = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.probeCardNo ) )
					probeCardNo = (String)parameterMap.get( ParameterTypes.probeCardNo );

				if( parameterMap.containsKey( ParameterTypes.system ) )
					sys = (String)parameterMap.get( ParameterTypes.system );

				if( parameterMap.containsKey( ParameterTypes.rmaCriteria ) )
					rmaCriteria = (String)parameterMap.get( ParameterTypes.rmaCriteria );

				if( parameterMap.containsKey( ParameterTypes.scrapCriteria ) )
					scrapCriteria = (String)parameterMap.get( ParameterTypes.scrapCriteria );

				sort.setExpectedNetDiePerWafer(expectedNetDieWafer);
				sort.setLotHoldYieldLimit(lotHoldYeildLimit);
				sort.setLotScrapYieldLimit( lotScrapYieldLimit );
				sort.setStndUnitCost( standardUnitCost );
				sort.setProbeCardNo( probeCardNo );
				sort.setSystem( sys );
				sort.setRmaCriteria( rmaCriteria );
				sort.setScrapCriteria( scrapCriteria );
				sort.setPoFrequency("");
				sort.setProcedureDocId(procedureDocId);
				sort.setProgramDocId( programDocId );
				sort.setProcessStepId( processStepId );

				if( flag )
				{
					SortProcessDAO.updateInSortTable( transaction , sort );
				}
				else
				{
					SortProcessDAO.saveInSortTable( transaction , sort );
				}
			}
			else if( processType.equals( ProcessTypes.assembly ) )
			{
				float standardUnitCost = 0.0f;
				float lotScrapYieldLimit = 0.0f;
				float targetYield = 0.0f;
				String packageID = "";
				String poFrequency="";
				int procedureDocId = 1;
				int bondingDocId = 1 ;
				int markingSpecId = 1;
				String dateCodeMarked = "1";

				if( parameterMap.containsKey( ParameterTypes.standardUnitCost ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.standardUnitCost ) != null) && ((String)parameterMap.get( ParameterTypes.standardUnitCost )).length() > 0 )
					{
						try{
							standardUnitCost = Float.parseFloat((String)parameterMap.get( ParameterTypes.standardUnitCost ));
						}catch( NumberFormatException e){
							standardUnitCost = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.lotScarpyeildLimit ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ) != null) && ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit )).length() > 0 ){
						try{
							lotScrapYieldLimit = Float.parseFloat((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ));
						}catch( NumberFormatException e){
							lotScrapYieldLimit = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.targetYeild ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.targetYeild ) != null) && ((String)parameterMap.get( ParameterTypes.targetYeild )).length() > 0 )
					{
						try{
							targetYield = Float.parseFloat((String)parameterMap.get( ParameterTypes.targetYeild ));
						}catch( NumberFormatException e){
							targetYield = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.packageID ) )
					packageID = (String)parameterMap.get( ParameterTypes.packageID );

				if( flag )
				{
					AssemblyProcessDAO.updateAssemblyProcessData (transaction, processStepId, packageID, standardUnitCost,targetYield, lotScrapYieldLimit, poFrequency, procedureDocId, bondingDocId, markingSpecId, dateCodeMarked);
				}
				else
				{
					AssemblyProcessDAO.storeAssemblyProcessData (transaction, processStepId, packageID, standardUnitCost,targetYield, lotScrapYieldLimit, poFrequency, procedureDocId, bondingDocId, markingSpecId, dateCodeMarked);
				}
			}
			else if( processType.equals( ProcessTypes.test ) )
			{
				double standardUnitCost = 0.0f;
				double lotHoldYeildLimit = 0.0f;
				double lotScrapYieldLimit = 0.0f;
				double targetYield = 0.0f;
				String sys = "";
				String poFrequency="";
				String testJig = "";
				int procedureDocID = 1;
				int programDocId = 1;

				if( parameterMap.containsKey( ParameterTypes.targetYeild ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.targetYeild ) != null) && ((String)parameterMap.get( ParameterTypes.targetYeild )).length() > 0 )
					{
						try{
							targetYield = Float.parseFloat((String)parameterMap.get( ParameterTypes.targetYeild ));
						}catch( NumberFormatException e){
							targetYield = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.lotHoldYeildLimit ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit ) != null) && ((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit )).length() > 0 )
					{
						try{
							lotHoldYeildLimit = Float.parseFloat((String)parameterMap.get( ParameterTypes.lotHoldYeildLimit ));
						}catch( NumberFormatException e){
							lotHoldYeildLimit = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.lotScarpyeildLimit ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ) != null) && ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit )).length() > 0 ){
						try{
							lotScrapYieldLimit = Float.parseFloat((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ));
						}catch( NumberFormatException e){
							lotScrapYieldLimit = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.standardUnitCost ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.standardUnitCost ) != null) && ((String)parameterMap.get( ParameterTypes.standardUnitCost )).length() > 0 )
					{
						try{
							standardUnitCost = Float.parseFloat((String)parameterMap.get( ParameterTypes.standardUnitCost ));
						}catch( NumberFormatException e){
							standardUnitCost = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.testJig ) )
					testJig = (String)parameterMap.get( ParameterTypes.testJig );

				if( parameterMap.containsKey( ParameterTypes.system ) )
					sys = (String)parameterMap.get( ParameterTypes.system );

				if( flag )
				{
					TestDAO.updateFinalTest ( transaction, processStepId, lotHoldYeildLimit, lotScrapYieldLimit, targetYield, standardUnitCost, poFrequency, procedureDocID, programDocId, testJig, sys);
				}
				else
				{
					TestDAO.insertFinalTestRow( transaction, processStepId, lotHoldYeildLimit, lotScrapYieldLimit, targetYield, standardUnitCost, poFrequency, procedureDocID, programDocId, testJig, sys);
				}
			}
			else if( processType.equals( ProcessTypes.markAndPack ) )
			{
				double standardUnitCost = 0.0f;
				double lotScrapYieldLimit = 0.0f;
				double targetYield = 0.0f;
				String visualSystem = "";
				String packingMethod = "";
				String poFrequency="";
				int procedureDocID = 1;
				int markingDiagId = 1;

				if( parameterMap.containsKey( ParameterTypes.standardUnitCost ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.standardUnitCost ) != null) && ((String)parameterMap.get( ParameterTypes.standardUnitCost )).length() > 0 )
					{
						try{
							standardUnitCost = Float.parseFloat((String)parameterMap.get( ParameterTypes.standardUnitCost ));
						}catch( NumberFormatException e){
							standardUnitCost = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.lotScarpyeildLimit ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ) != null) && ((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit )).length() > 0 ){
						try{
							lotScrapYieldLimit = Float.parseFloat((String)parameterMap.get( ParameterTypes.lotScarpyeildLimit ));
						}catch( NumberFormatException e){
							lotScrapYieldLimit = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.targetYeild ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.targetYeild ) != null) && ((String)parameterMap.get( ParameterTypes.targetYeild )).length() > 0 )
					{
						try{
							targetYield = Float.parseFloat((String)parameterMap.get( ParameterTypes.targetYeild ));
						}catch( NumberFormatException e){
							targetYield = 0.0f;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.visualSystem ) )
					visualSystem = (String)parameterMap.get( ParameterTypes.visualSystem );

				if( parameterMap.containsKey( ParameterTypes.packingMethod ) )
					packingMethod = (String)parameterMap.get( ParameterTypes.packingMethod );

				if( flag )
				{
					MarkAndPackDAO.updateMarkAndPackData(transaction, processStepId, standardUnitCost, poFrequency, targetYield, lotScrapYieldLimit, procedureDocID, visualSystem, markingDiagId, packingMethod);
				}
				else
				{
					MarkAndPackDAO.storeMarkAndPackData (transaction, processStepId, standardUnitCost, poFrequency, targetYield, lotScrapYieldLimit, procedureDocID, visualSystem, markingDiagId, packingMethod);
				}
			}
			else if( processType.equals( ProcessTypes.finishedGoods ) )
			{
				int minLevel = 0;
				int maxLevel = 0;
				int reOrderLevel = 0;

				if( parameterMap.containsKey( ParameterTypes.minLevel ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.minLevel ) != null) && ((String)parameterMap.get( ParameterTypes.minLevel )).length() > 0 ){
						try{
							minLevel = Integer.parseInt((String)parameterMap.get( ParameterTypes.minLevel ));
						}catch( NumberFormatException e){
							minLevel = 0;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.maxLevel ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.maxLevel ) != null) && ((String)parameterMap.get( ParameterTypes.maxLevel )).length() > 0 ){
						try{
							maxLevel = Integer.parseInt((String)parameterMap.get( ParameterTypes.maxLevel ));
						}catch( NumberFormatException e){
							maxLevel = 0;
						}
					}
				}

				if( parameterMap.containsKey( ParameterTypes.reorderLevel ) )
				{
					if( ((String)parameterMap.get( ParameterTypes.reorderLevel ) != null) && ((String)parameterMap.get( ParameterTypes.reorderLevel )).length() > 0 ){
						try{
							reOrderLevel = Integer.parseInt((String)parameterMap.get( ParameterTypes.reorderLevel ));
						}catch( NumberFormatException e){
							reOrderLevel = 0;
						}
					}
				}

				if( flag )
				{
					updateFinalInventoryData( transaction, processStepId, minLevel, maxLevel, reOrderLevel, partNumber, mktPartNumber );
				}
				else
				{
					//opearations.finishedgoods.insertNew=INSERT INTO Inventory_Step_Table (ProcessStep_ID, IST_Min_Level, IST_Max_Level, IST_Reorder_Level, IST_Manufacturing_Part_Number, IST_Marketing_Part_Number, Timestamp) VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
					storeFinalInventoryData ( transaction, processStepId, minLevel, maxLevel, reOrderLevel, partNumber, mktPartNumber );
				}
			}
		}
		catch(Exception e )
		{
			e.printStackTrace();
			throw new ScalarException("Process Information not stored to database",e );
		}
	}

    public static String getDuplicatePartNumbers( String productNameParameter ) throws NumberFormatException, ScalarException
    {
		String duplicateNames 	= "";	// The return String with Duplicate Part Numbers as String
		String processStep_Id 	= "";	// ProcessStepID of the Process
		String processType 		= "";	// ProcessType of the Process
		String partNumbers 		= "";	// comma separated names of the product
		ArrayList productList 		= new ArrayList();	// Temporary Variable which stores the PartNumbers
		ArrayList processParameters = new ArrayList();  // Temporary Variable which stores the processStepId, ProcessType, and PartNumbers
		String[] arrProductNames ;

		if( productNameParameter != null && productNameParameter.length() > 0 )
		{
			processParameters = parseString( productNameParameter,';' );

			if( processParameters.size() == 3 )
			{
				processStep_Id 	= (String) processParameters.get( 0 );
				partNumbers		= (String) processParameters.get( 1 );
				processType 	= (String) processParameters.get( 2 );
				productList 	= parseString( partNumbers ,',' );
				arrProductNames = new String[ productList.size() ];

				if( processStep_Id == null || processStep_Id.length() < 1 )
				{
					processStep_Id = "-1";
				}

				for ( int i = 0 ; i < productList.size() ; i++ )
				{
					arrProductNames[i] = (String)productList.get( i );
				}

				if( arrProductNames.length > 0 )
				{
					duplicateNames = ProductDao.checkDupInProductMapTable( arrProductNames , Integer.parseInt(processStep_Id), processType );
				}
			}
		}
        return duplicateNames;
    }

    //Added by Mallik Ankati 03/04/2004
    //Begin
    /*public static LinkedHashMap getLotgenealogy(String lotId, String lotNumber) throws ScalarException{

    	Connection conn  = null;
		ResultSet rs     = null;
		PreparedStatement pstmt = null;
		ArrayList lotviewList = new ArrayList();

		LinkedHashMap map = new LinkedHashMap();

		try
		{
			conn = DataSource.getConnection();

			pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.process.lottrace") );
			pstmt.setString(1,lotId);
			pstmt.setString(2, lotNumber);

			rs = pstmt.executeQuery();
			int index = 0;
			while(rs.next())
			{
				LotView lotView = new LotView();
				lotView.setSerialNumber(rs.getInt("S_No"));
				lotView.setLotNumber (rs.getString("Lot_Number"));
				lotView.setPartNumber(rs.getString("Part_Number"));
				lotView.setCurrentProcess(rs.getString("PS_Type"));
				lotView.setProcessTypeName(rs.getString("PS_Type_Name"));
				lotView.setVendorLocationName(rs.getString("Location"));
				lotView.setWaferQtyIn(rs.getInt("Wafer_Qty_In"));
				lotView.setWaferQtyOut(rs.getInt("Wafer_Qty_Out"));
				lotView.setGrossDie(rs.getInt("Gross_Die"));
				lotView.setQuantityIn(rs.getInt("Qty_In"));
				lotView.setQuantityOut(rs.getInt("Qty_Out"));
    			lotView.setCurrentYield(rs.getFloat("Yield"));
    			lotView.setDateIn(StringUtils.formatDateToString(StringUtils.format(rs.getString("Date_In"), "yyyy-MM-dd"), "MM/dd/yyyy"));
    			lotView.setDateOut(StringUtils.formatDateToString(StringUtils.format(rs.getString("Date_Out"), "yyyy-MM-dd"), "MM/dd/yyyy"));
    			lotView.setSplitFlag(rs.getInt("Split_Flag"));
    			lotView.setMergeFlag(rs.getInt("Merge_Flag"));
    			lotView.setSplitMergeId(rs.getInt("SplitMerge_Id"));
    			lotView.setSalesOrderNumber(rs.getString("SalesOrder_NO"));
    			lotView.setCustomerName(rs.getString("Customer_Name"));
    			lotView.setActualShipDate(StringUtils.formatDateToString(StringUtils.format(rs.getString("Actual_Ship_Date"), "yyyy-MM-dd"), "MM/dd/yyyy"));
        		lotView.setShipQty(rs.getInt("Ship_Qty"));
                lotView.setVendorLotNumber(rs.getString("Vendor_Lot_Number") == null ? "" : rs.getString("Vendor_Lot_Number"));//Added by KumaraSwamy on August 5th, 2004
                lotView.setLotComments(rs.getString("Lot_Comments") == null ? "" : rs.getString("Lot_Comments")); //Added by KumaraSwamy on August 5th, 2004
                lotView.setProgReleaseNo( rs.getString("PRG_REL_NO"));


                if( ( lotView.getSalesOrderNumber() != null ) && ( !"null".equalsIgnoreCase(lotView.getSalesOrderNumber() ) ) )
                {
    			    ArrayList ship_list = (ArrayList)map.get("ship");

    				if( ship_list == null )
                    {
    					ArrayList list = new ArrayList();
    					list.add( lotView );
    					map.put( "ship", list );
    				}
                    else
                    {
    					ship_list.add( lotView );
    					map.put( "ship", ship_list );
    				}
    			}
                else
                {
                    ArrayList t_list = (ArrayList)map.get(lotView.getCurrentProcess());
                    if( t_list == null )
                    {
                        ArrayList list = new ArrayList();
                        list.add( lotView );
                        map.put( lotView.getCurrentProcess(), list );
                    }
                    else
                    {
                        t_list.add( lotView );
                        map.put( lotView.getCurrentProcess(), t_list );
                    }
                }
    			*//*ArrayList t_list = (ArrayList)map.get(lotView.getCurrentProcess());

    			if(t_list == null){

    				ArrayList list = new ArrayList();

    				list.add(lotView);
    				map.put(lotView.getCurrentProcess(), list);

    			}else{

    				t_list.add(lotView);
    				map.put(lotView.getCurrentProcess(), t_list);
    			}

    			if((lotView.getSalesOrderNumber() != null )
    				&& (!"null".equalsIgnoreCase(lotView.getSalesOrderNumber()))){
    					ArrayList ship_list = (ArrayList)map.get("ship");

    					if(ship_list == null){

    					ArrayList list = new ArrayList();

    					list.add(lotView);
    					map.put("ship", list);

    				}else{
    					ship_list.add(lotView);
    					map.put("ship", ship_list);
    				}
    			}*//*
			}

			index++;
		}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException("getLotgenealogy() method failed ", sql);
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

		return map;
    }
*/
    /**
     * @return HashMap of all the Processes and there Corresponding Parameters
     */
    public static HashMap getAllProcessParameters()
    {
    	HashMap processListMap	= new HashMap();
		HashMap processMap 		= null;
		Vector parameter		= null;
		Vector parameterList	= null;
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;
		Iterator it				= null;
		String processType		= null;

		try
		{
			conn 	= DataSource.getConnection();
			pstmt 	= conn.prepareStatement( "select ProcessTypeID, Process_Type, Base_Process_type from Process_Types_Table order by Process_Type" );
			rs		= pstmt.executeQuery();

			while( rs.next() )
			{
				processMap = new HashMap();
				processMap.put( "ProcessTypeID", rs.getString( "ProcessTypeID" ) );
				processMap.put( "Process_Type" , rs.getString( "Process_Type" ) );
				processMap.put( "Base_Process_type", rs.getString( "Base_Process_type" ) );
				processListMap.put( processMap.get("Process_Type"), processMap );
			}

			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			if( processListMap != null && processListMap.isEmpty() == false )
			{
				it	= processListMap.keySet().iterator();
				while( it.hasNext() )
				{
					processType 	= ( String ) it.next();
					processMap		= ( HashMap )processListMap.get( processType );
					parameterList 	= new Vector();
					pstmt	= conn.prepareStatement( "select DPP_Parameter, DPP_Parameter_Units, DPP_Parameter_value from Default_Process_Parameter_Table where ProcessTypeId = ? " );
					pstmt.setInt( 1, Integer.parseInt( (String)processMap.get( "ProcessTypeID" ) ) );
					rs		= pstmt.executeQuery();

					while( rs.next() )
					{
						parameter = new Vector();
						parameter.add( rs.getString( "DPP_Parameter" ) );
						parameter.add( rs.getString( "DPP_Parameter_Units" ) );
						parameter.add( rs.getString( "DPP_Parameter_value" ) );
						parameterList.add( parameter );
					}
					processMap.put( "parameters", parameterList );

					rs.close();
					rs = null;
					pstmt.clearParameters();
					pstmt.close();
					pstmt = null;
				}
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
		}
		finally
		{
			if( rs != null )
			{
				try{ rs.close(); }catch( SQLException ignore ){}
			}
			if( pstmt != null )
			{
				try{ pstmt.close(); }catch( SQLException ignore ){}
			}
			if( conn != null ){
				try{ conn.close(); }catch( SQLException ignore ){}
			}
		}
		return processListMap;
    }

    /**
     * @param productNameParameter
     * @return
     */
    public static String getDuplicateMktPartNumbers( String productNameParameter ) throws ScalarException
    {
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;
		String duplicateNames 	= "";	// The return String with Duplicate Part Numbers as String
		String processStep_Id 	= "";	// ProcessStepID of the Process
		String processType 		= "";	// ProcessType of the Process
		String partNumber 		= "";	// marketing Part Number of the product
		ArrayList processParameters = new ArrayList();  // Temporary Variable which stores the processStepId, ProcessType, and PartNumbers

		try
		{
			if( productNameParameter != null && productNameParameter.length() > 0 )
			{
				processParameters = parseString( productNameParameter,';' );

				if( processParameters.size() == 3 )
				{
					processStep_Id 	= (String) processParameters.get( 0 );
					partNumber		= (String) processParameters.get( 1 );
					processType 	= (String) processParameters.get( 2 );

					if( processStep_Id == null || processStep_Id.length() < 1 )
					{
						processStep_Id = "-1";
					}

					if( partNumber != null && partNumber.length() > 0 )
					{
						conn = DataSource.getConnection();
						pstmt= conn.prepareStatement( "select P_Marketing_Part_Number from Product where P_Marketing_Part_Number = ? and ProcessStep_ID <> ?" );
						pstmt.setString( 1, partNumber );
						pstmt.setString( 2, processStep_Id );

						rs = pstmt.executeQuery();
						if( rs.next() )
						{
							duplicateNames = rs.getString( "P_Marketing_Part_Number" );
						}
					}
				}
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not check for Duplicate Marketing Part Number" );
		}
		finally
		{
			if( rs != null )
			{
				try{ rs.close(); }catch( SQLException ignore ){}
			}
			if( pstmt != null )
			{
				try{ pstmt.close(); }catch( SQLException ignore ){}
			}
			if( conn != null ){
				try{ conn.close(); }catch( SQLException ignore ){}
			}
		}
		return duplicateNames;
    }

    /**
     * @param processStepId
     * @return vendorsWithLots
     */
    public static ArrayList getVendorsWithLots( String processStepId ) throws ScalarException
    {
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;
        int processStepIdInt    = 0;
		ArrayList vendorsWithLots = new ArrayList(5);

        try {
            processStepIdInt = Integer.parseInt( processStepId );
        }
        catch ( NumberFormatException ignore )
        {

        }

		try
		{
			if ( processStepIdInt > 0 )
			{
                conn = DataSource.getConnection();
                pstmt= conn.prepareStatement( "SELECT DISTINCT V.V_NAME + '/' + CITY as VendorLocation FROM  LOT_TABLE LT, LOCATION L, VENDOR V, ADDRESS A  WHERE LT.LOCATION_ID = L.LOCATION_ID AND V.VENDOR_ID = L.VENDOR_ID AND L.ADDRESS_ID = A.ADDRESS_ID AND ( LOT_CURRENT_STATUS = 'Running' or LOT_CURRENT_STATUS = 'Hold' ) AND LT.PROCESSSTEP_ID = ?" );
                pstmt.setInt( 1, processStepIdInt );
                rs = pstmt.executeQuery();

                while( rs.next() )
                {
                      vendorsWithLots.add( rs.getString("VendorLocation") );
                }
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not load List Of Vendors haing Lots for the ProcessStepId " + processStepIdInt );
		}
		finally
		{
			if( rs != null )
			{
				try{ rs.close(); }catch( SQLException ignore ){}
			}
			if( pstmt != null )
			{
				try{ pstmt.close(); }catch( SQLException ignore ){}
			}
			if( conn != null ){
				try{ conn.close(); }catch( SQLException ignore ){}
			}
		}
		return vendorsWithLots;
    }
    public static LinkedHashMap getLotgenealogy( String lotNumber, String lotType ) throws ScalarException
    {
        Connection conn         = null;
		ResultSet rs            = null;
		PreparedStatement pstmt = null;
		LinkedHashMap map       = new LinkedHashMap();

		try
		{
			conn = DataSource.getConnection();
            pstmt = conn.prepareCall ( "{ call sp_lot_trace_by_lot_number_by_type ( ?, ? ) }" );
			//pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.process.lottrace") );
			pstmt.setString(1, lotNumber);
            pstmt.setString(2, lotType);

            rs = pstmt.executeQuery();

            while(rs.next())
			{
				LotView lotView = new LotView();


                lotView.setFabLotNumber( rs.getString("Fab_Lot_Number") );
                if ( lotType.equalsIgnoreCase("fabLot") == false )
                {
                    lotView.setLotNumber(rs.getString("Lot_Number") );
                    lotView.setLotID( rs.getString("LOT_ID") );
                }
                lotView.setPartNumber(rs.getString("Part_Number") );
				lotView.setCurrentProcess(rs.getString("PS_Type") );
				lotView.setProcessTypeName(rs.getString("PS_Type_Name") );
				lotView.setVendorLocationName(rs.getString("Vendor_Name") );
				lotView.setWaferQtyIn(rs.getInt("Wafer_Qty_In") );
				lotView.setWaferQtyOut(rs.getInt("Wafer_Qty_Out") );
				lotView.setQuantityIn(rs.getInt("Qty_In") );
				lotView.setQuantityOut(rs.getInt("Qty_Out") );
    			lotView.setCurrentYield(rs.getFloat("Yield") );
    			lotView.setDateIn(StringUtils.formatDate (rs.getString("Date_In"), "yyyy-MM-dd", "MM/dd/yyyy") );
    			lotView.setDateOut(StringUtils.formatDate(rs.getString("Date_Out"), "yyyy-MM-dd", "MM/dd/yyyy") );
                lotView.setLotCategory( rs.getString("Lot_Category") );
                lotView.setLotComments(rs.getString("Lot_Comments") == null ? "" : rs.getString("Lot_Comments"));
                lotView.setShipToVendorName(rs.getString("Ship_To_Location") == null ? "" : rs.getString("Ship_To_Location"));
                lotView.setTestProgram( rs.getString("Test_Program") == null ? "" : rs.getString("Test_Program") );
                lotView.setLotStatus( rs.getString("Lot_Status") );
                lotView.setHoldReason( rs.getString("Hold_Reason") == null ? "" : rs.getString("Hold_Reason") );
                lotView.setGoodDie( rs.getString("Good_Die") == null ? "" : rs.getString("Good_Die") );
                lotView.setDateCode( rs.getString("Date_Code") == null ? "" : rs.getString("Date_Code") );
                lotView.setSplitFlag( rs.getInt("Lot_Split") );
                lotView.setUserName( rs.getString("User_Name") == null ? "" : rs.getString("User_Name"));
                lotView.setBomLotNo( rs.getString("Bom_Lot_No") == null ? "" : rs.getString("Bom_Lot_No"));
                lotView.setBomLotQty( rs.getString("Bom_Qty") == null ? "" : rs.getString("Bom_Qty") );

                lotView.setSalesOrderNumber(rs.getString("SALES_ORDER_NO"));
    			lotView.setCustomerName(rs.getString("CUST_NAME"));
                lotView.setActualShipDate(StringUtils.formatDate(rs.getString("CUST_SHIP_DATE"), "yyyy-MM-dd", "MM/dd/yyyy"));
        		lotView.setShipQty(rs.getInt("CUST_SHIP_QTY"));

                if( ( lotView.getSalesOrderNumber() != null ) && ( !"null".equalsIgnoreCase(lotView.getSalesOrderNumber() ) ) )
                {
    			    ArrayList ship_list = (ArrayList)map.get("ship");
                    if( ship_list == null )
                    {
    					ArrayList list = new ArrayList();
    					list.add( lotView );
    					map.put( "ship", list );
    				}
                    else
                    {
    					ship_list.add( lotView );
    					map.put( "ship", ship_list );
    				}
    			}
                else
                {
                    ArrayList t_list = (ArrayList)map.get(lotView.getCurrentProcess());
                    if( t_list == null )
                    {
                        ArrayList list = new ArrayList();
                        list.add( lotView );
                        map.put( lotView.getCurrentProcess(), list );
                    }
                    else
                    {
                        t_list.add( lotView );
                        map.put( lotView.getCurrentProcess(), t_list );
                    }
                }
			}

		}
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException("getLotgenealogy() method failed ", sql);
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return map;
    }

    /**
     *
     * @param processStepId
     * @return
     * @throws ScalarException
     */
    public static HashMap getActivityAttributesMap( DBTransaction dbTransaction, int processStepId ) throws ScalarException
	{
        HashMap activityMap = new HashMap();
    	Connection conn = null;
    	PreparedStatement pstmt = null;
    	ResultSet rs = null;
    	try
		{
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT PART_NUMBER, P_MARKETING_PART_NUMBER, P_COMMENTS, IS_APPROVED, PART_STATUS, PRODUCT_GROUP, IS_PRIMARY_FG FROM PRODUCT WHERE PROCESSSTEP_ID = ?" );
            pstmt.setInt ( 1, processStepId );
            rs = pstmt.executeQuery();
            String partnumber = "";
            String mktPartNumber = "";
            String comments = "";
            String partStatus = "";
            String productGroup = null;
            boolean isApproved =  false;
            boolean isPrimaryFGProduct = false;
            if ( rs.next() )
            {
                partnumber = rs.getString ( "PART_NUMBER" );
                mktPartNumber = rs.getString ( "P_MARKETING_PART_NUMBER" );
                comments = rs.getString ( "P_COMMENTS" );
                isApproved = ( rs.getInt ( "IS_APPROVED" ) == 0 );
                partStatus =  rs.getString ( "PART_STATUS" );
                productGroup = rs.getString ( "PRODUCT_GROUP" );
                isPrimaryFGProduct = ( rs.getInt ( "IS_PRIMARY_FG" ) == 0 );
            }
            activityMap.put( "partnumber", partnumber==null ? "" : partnumber );
            activityMap.put( "mktPartNumber", mktPartNumber==null ? "" : mktPartNumber );
            activityMap.put( "comments", comments==null ? "" : comments );
            activityMap.put( "partStatus", partStatus==null ? "" : partStatus );
            activityMap.put( "productGroup", productGroup==null ? "" : productGroup );
            activityMap.put( "isApprovedProduct", Boolean.toString( isApproved ) );
            activityMap.put( "isPrimaryFGProduct", Boolean.toString( isPrimaryFGProduct ) );

            if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
				pstmt = null;
    		}
            pstmt = conn.prepareStatement( "SELECT PARAMETER_KEY + '=' + PARAMETER_UNITS + '='+PARAMETER_VALUE + ';' AS PARAMETER FROM PROCESS_PARAMETER(NOLOCK) WHERE PROCESSSTEP_ID = ?" );
    		pstmt.setInt( 1, processStepId );
    		rs = pstmt.executeQuery();
    		String parameters = "";
    		while ( rs.next() )
    		{
                parameters += rs.getString( "PARAMETER" );
            }
            activityMap.put ( "parameters", parameters );
    		if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
				pstmt = null;
    		}

    		pstmt = conn.prepareStatement( "SELECT DISTINCT PART_NUMBER + ';' AS PART_NUMBER FROM PRODUCT_MAP_TABLE(NOLOCK) WHERE PROCESSSTEP_ID = ?" );
    		pstmt.setInt( 1, processStepId );
    		rs = pstmt.executeQuery();
    		String equiPartNumbers = "";
    		while ( rs.next() )
    		{
                equiPartNumbers += rs.getString( "PART_NUMBER" );
            }
            activityMap.put ( "dupPartNumbers", equiPartNumbers );

    		if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
				pstmt = null;
    		}

    		pstmt = conn.prepareStatement( "SELECT PSLT.PROCESSSTEP_ID, 'TRUE;' + CONVERT( VARCHAR, PSLT.PSLT_PRIORITY ) + ';' + (L.L_VENDOR_NAME + '/' + A.CITY) + ';' + CONVERT( VARCHAR, PSLT.PSLT_SPLIT ) +';'+ CONVERT( VARCHAR, PSLT.PSLT_CAPACITY ) +';'+ CONVERT( VARCHAR, PSLT.PSLT_CYCLE_TIME_NORMAL ) +';'+ CONVERT( VARCHAR, PSLT.PSLT_CYCLE_TIME_EXPRESS ) +'#' AS VENDORS FROM PROCESS_STEP_LOCATION_TABLE PSLT(NOLOCK), LOCATION L(NOLOCK), ADDRESS A(NOLOCK) WHERE PSLT.LOCATION_ID = L.LOCATION_ID  AND L.ADDRESS_ID = A.ADDRESS_ID AND PSLT.PROCESSSTEP_ID = ?" );
    		pstmt.setInt( 1, processStepId );
    		rs = pstmt.executeQuery();
    		String processVendorLocations = "";
    		while ( rs.next() )
    		{
                processVendorLocations += rs.getString( "VENDORS" );
            }
            activityMap.put ( "vendors", processVendorLocations );
            if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
				pstmt = null;
    		}
            int goodBinNumber = 0;
            int rejectedBinNumber = 0;
            boolean binsRequired = false;
            pstmt = conn.prepareStatement ( "SELECT COUNT(*) AS GOOD_BIN_NUMBER FROM WIP_STEP_BIN_TABLE WHERE PROCESSSTEP_ID = ? AND WSBT_BIN_TYPE = 'Accept'" );
            pstmt.setInt ( 1, processStepId );
            rs = pstmt.executeQuery();
            if ( rs.next() )
            {
                goodBinNumber = rs.getInt ( "GOOD_BIN_NUMBER" );
            }
            if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
				pstmt = null;
    		}
            pstmt = conn.prepareStatement ( "SELECT COUNT(*) AS REJECTED_BIN_NUMBER FROM WIP_STEP_BIN_TABLE WHERE PROCESSSTEP_ID = ? AND WSBT_BIN_TYPE = 'N/A'" );
            pstmt.setInt ( 1, processStepId );
            rs = pstmt.executeQuery();
            if ( rs.next() )
            {
                rejectedBinNumber = rs.getInt ( "REJECTED_BIN_NUMBER" );
            }
            if ( goodBinNumber > 0 || rejectedBinNumber > 0 )
            {
                binsRequired = true;
            }
            activityMap.put("goodBinNumber", goodBinNumber+"");
            activityMap.put("rejectedBinNumber", rejectedBinNumber+"");
            activityMap.put("binsRequired", Boolean.toString( binsRequired ) );
        }
    	catch( Exception e )
		{
    		e.printStackTrace();
    		throw new ScalarException( "Could not fetch the Activity information for processStepId=" + processStepId );
		}
    	finally
		{
            if( rs != null )
            {
                try
                {
                    rs.close(); rs = null;
                } catch ( SQLException ignore ) {}
            }
            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
    				pstmt = null;
                } catch ( SQLException ignore ) {}
            }
            if( conn != null )
    		{
                try
                {
                    conn.close();
    				conn = null;
    			}
                catch ( SQLException ignore ) {}
            }

		}

    	return activityMap;
	}
    public String deleteProcess ( DBTransaction dbTransaction, int processStepId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        String msg = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "{CALL SP_DELETE_PROCESS_STEP( ?, ? )}" );
            cstmt.setInt ( 1, processStepId );
            cstmt.registerOutParameter( 2, Types.VARCHAR );
            cstmt.execute();
            msg = cstmt.getString ( 2 );
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException( "Could not delete process", e );
        }
        finally
        {
            if ( dbTransaction == null && !success && conn != null )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return msg;
    }

    public static HashMap getActivityMap( String baseProductName ) throws ScalarException
	{
    	HashMap activityMap = new HashMap();
    	HashMap activity = null;
    	Connection conn = null;
    	PreparedStatement pstmt = null;
    	ResultSet rs = null;
    	String processStep_Id = "";
    	StringBuffer parameterString = new StringBuffer();
    	String prevProcessStep_id = "";
    	StringBuffer equivalentPartNumber = new StringBuffer();
    	StringBuffer vendors = new StringBuffer();

    	try
		{
    		conn = DataSource.getConnection();
    		pstmt = conn.prepareStatement( "SELECT P.PART_NUMBER, P.PROCESSSTEP_ID, P_MARKETING_PART_NUMBER, PS.PS_TYPE, PS.PS_TYPE_NAME, P.P_COMMENTS, P.OBSOLETE, P.IS_APPROVED FROM PRODUCT P(NOLOCK), PROCESS_STEP PS(NOLOCK) WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P_PARENT_PRODUCT_ID = ( SELECT PRODUCT_ID FROM PRODUCT(NOLOCK) WHERE PART_NUMBER = ? AND P_PARENT_PRODUCT_ID IS NULL ) ORDER BY P.PROCESSSTEP_ID" );
    		pstmt.setString( 1, baseProductName );
    		rs = pstmt.executeQuery();

    		while ( rs.next() )
    		{
    			activity = new HashMap();
    			activity.put( "partnumber", rs.getString( "PART_NUMBER" ) );
    			processStep_Id = rs.getString( "PROCESSSTEP_ID" );
    			activity.put( "processStepId", processStep_Id );
                activity.put ( "mktPartNumber", rs.getString( "P_MARKETING_PART_NUMBER" ) );
                activity.put( "value", rs.getString( "PS_TYPE" ) );
    			activity.put( "Type", rs.getString( "PS_TYPE_NAME" ) );
    			activity.put( "comments", rs.getString( "P_COMMENTS" ) );
    			/*activity.put( "poPartNumber", rs.getString( "P_Po_Part_Number" ) );
    			activity.put( "shopOrderPartNumber", rs.getString( "P_Shop_Order_Part_Number" ) );*/
                activity.put( "obsolete", rs.getString ( "OBSOLETE" ) );
                activity.put ( "isApprovedProduct", Boolean.toString( rs.getInt ( "IS_APPROVED" ) == 0 ) );
                activityMap.put( processStep_Id, activity );
    		}

    		if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
				pstmt = null;
    		}

    		pstmt = conn.prepareStatement( "SELECT PROCESSSTEP_ID, PARAMETER_KEY + '=' + PARAMETER_UNITS + '='+PARAMETER_VALUE + ';' AS Parameter FROM Process_Parameter(NOLOCK) WHERE PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM PRODUCT(NOLOCK) WHERE P_PARENT_PRODUCT_ID = ( SELECT PRODUCT_ID FROM PRODUCT(NOLOCK) WHERE PART_NUMBER = ? AND P_PARENT_PRODUCT_ID IS NULL ) ) ORDER BY PROCESSSTEP_ID" );
    		pstmt.setString( 1, baseProductName );
    		rs = pstmt.executeQuery();
    		String parameters = "";
    		while ( rs.next() )
    		{
                processStep_Id = rs.getString( "PROCESSSTEP_ID" );
                String parameter = rs.getString( "Parameter" );
                if ( activityMap.containsKey(processStep_Id) )
                {
                    activity = (HashMap)activityMap.get ( processStep_Id );
                    if ( activity.containsKey("parameters") )
                    {
                        parameters = (String)activity.get("parameters");
                        activity.put ( "parameters", (parameters+parameter) );
                    }
                    else
                    {
                        activity.put ( "parameters", parameter );
                    }
                }
            }

    		if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
				pstmt = null;
    		}

    		pstmt = conn.prepareStatement( "SELECT DISTINCT PROCESSSTEP_ID, PART_NUMBER + ';' as PART_NUMBER FROM PRODUCT_MAP_TABLE(NOLOCK) WHERE PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM PRODUCT(NOLOCK) WHERE P_PARENT_PRODUCT_ID = ( SELECT PRODUCT_ID FROM PRODUCT(NOLOCK) WHERE PART_NUMBER = ? AND P_PARENT_PRODUCT_ID IS NULL ) ) ORDER BY PROCESSSTEP_ID" );
    		pstmt.setString( 1, baseProductName );
    		rs = pstmt.executeQuery();
    		prevProcessStep_id = "";
    		String equiPartNumbers = "";
    		while ( rs.next() )
    		{
                processStep_Id = rs.getString( "PROCESSSTEP_ID" );
                String equiPartNumber = rs.getString( "PART_NUMBER" );
                if ( activityMap.containsKey(processStep_Id) )
                {
                    activity = (HashMap)activityMap.get ( processStep_Id );
                    if ( activity.containsKey("dupPartNumbers") )
                    {
                        equiPartNumbers = (String)activity.get("dupPartNumbers");
                        activity.put ( "dupPartNumbers", (equiPartNumbers+equiPartNumber) );
                    }
                    else
                    {
                        activity.put ( "dupPartNumbers", equiPartNumber );
                    }
                }
            }

    		if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
				pstmt = null;
    		}

    		pstmt = conn.prepareStatement( "select PSLT.processStep_Id, 'true;' + convert( varchar, ISNULL(PSLT.PSLT_Priority,'') ) + ';' + (L.L_Vendor_Name + '/' + ISNULL(A.City,'')) + ';' + convert( varchar, ISNULL(PSLT.PSLT_Split,'') ) +';'+ convert( varchar, ISNULL(PSLT.PSLT_Capacity,'') ) +';#' AS VENDORS from Process_Step_Location_table PSLT(NOLOCK), Location L(NOLOCK), Address A(NOLOCK) where PSLT.Location_Id = L.Location_ID  and L.Address_ID = A.Address_id and PSLT.ProcessStep_Id IN ( SELECT PROCESSSTEP_ID FROM PRODUCT(NOLOCK) WHERE P_PARENT_PRODUCT_ID = ( SELECT PRODUCT_ID FROM PRODUCT(NOLOCK) WHERE PART_NUMBER = ? AND P_PARENT_PRODUCT_ID IS NULL ) ) ORDER BY PROCESSSTEP_ID" );
    		pstmt.setString( 1, baseProductName );
    		rs = pstmt.executeQuery();
    		prevProcessStep_id = "";
    		String processVendors;
    		while ( rs.next() )
    		{
                processStep_Id = rs.getString( "PROCESSSTEP_ID" );
                String vendor = rs.getString( "VENDORS" );
                
                if ( activityMap.containsKey(processStep_Id) )
                {
                    activity = (HashMap)activityMap.get ( processStep_Id );
                    if ( activity.containsKey("vendors") )
                    {
                        processVendors = (String)activity.get("vendors");
                        activity.put ( "vendors", (processVendors+vendor) );
                    }
                    else
                    {
                        activity.put ( "vendors", vendor );
                    }
                }
            }

		}
    	catch( Exception e )
		{
    		e.printStackTrace();
    		throw new ScalarException( "Could not fetch the XML information for Product " + baseProductName );
		}
    	finally
		{
    		try
			{
    			if( rs != null )
    			{
    				rs.close();
    				rs = null;
    			}
    			if( pstmt != null )
    			{
    				pstmt.close();
    				pstmt = null;
    			}
    			if( conn != null )
    			{
    				conn.close();
    				conn = null;
    			}
			}
    		catch (SQLException e1)
			{
				e1.printStackTrace();
			}
		}

    	return activityMap;
	}

    public static ArrayList getTransitionMap( String baseProductName, HashMap activityCollectionMap ) throws ScalarException
	{
    	Connection conn 		= null;
    	PreparedStatement pstmt = null;
    	ResultSet rs			= null;
    	HashMap transitionMap 	= null;
    	ArrayList transitionList = new ArrayList();
    	HashMap processStepMap 	= new HashMap();
    	Iterator processStepMapIterator = null;
    	String processStep_Id 	= null;
    	String prevProcessStep_Id = null;
    	String next_processStep_Id = null;
    	ArrayList nextProcessStepList = null;
    	String edgeId 			= baseProductName+"_Tran_";
    	Rectangle transitionRect  = new Rectangle( 10, 10, 20, 20 );
    	Rectangle fromActivityRect 	= null;
    	Rectangle toActivityRect 	= null;
    	HashMap fromActivityMap = null;
    	HashMap toActivityMap = null;
    	int repeatIdx = 0;
    	int fabX = 50;
    	int fabY = 50;
    	int id = 1;

    	try
		{
    		conn = DataSource.getConnection();
    		pstmt = conn.prepareStatement( "{ call GET_PROCESS_FLOW_FROM_DB( ? ) }" );
    		pstmt.setString( 1, baseProductName );
    		rs = pstmt.executeQuery();
    		while( rs.next() )
    		{
    			processStep_Id = rs.getString( "ProcessStep_ID" );
    			next_processStep_Id = rs.getString( "Next_ProcessStep_ID" );

    			fromActivityMap = (HashMap)activityCollectionMap.get( processStep_Id );
    			toActivityMap = (HashMap)activityCollectionMap.get( next_processStep_Id );

				transitionMap = new HashMap();
				transitionMap.put( "From", fromActivityMap.get( "attrId" ) );
				transitionMap.put( "To", toActivityMap.get( "attrId" ) );
				transitionMap.put( "edgeId", edgeId + ( id++ ) );
				transitionMap.put( "bounds", transitionRect );
				transitionList.add( transitionMap );

				if( !fromActivityMap.containsKey( "bounds" ) )
				{
					fromActivityRect = new Rectangle( fabX, fabY, 120, 30 );
					fromActivityMap.put( "bounds", fromActivityRect );
					fabY = fabY + 50;
				}
				else
				{
					fromActivityRect = (Rectangle)fromActivityMap.get( "bounds" );
				}

				if( processStep_Id.equalsIgnoreCase( prevProcessStep_Id ) )
				{
					repeatIdx++;
				}
				else
				{
					repeatIdx = 0;
				}

				if( !toActivityMap.containsKey( "bounds" ) && ( repeatIdx > 0 ) )
				{
					toActivityRect = new Rectangle( fromActivityRect.x + 150, fabY , 120, 30 );
					toActivityMap.put( "bounds", toActivityRect );
					fabY = fabY + 50;
				}

				if( !toActivityMap.containsKey( "bounds" ) && repeatIdx == 0 )
				{
					toActivityRect = new Rectangle( fromActivityRect.x + 150, fromActivityRect.y, 120, 30 );
					toActivityMap.put( "bounds", toActivityRect );
				}
				prevProcessStep_Id = processStep_Id;
    		}
    		if( rs != null )
    		{
    			rs.close();
    			rs = null;
    		}
    		if( pstmt != null )
    		{
    			pstmt.close();
    			pstmt = null;
    		}
    		if( conn != null )
    		{
    			conn.close();
    			conn = null;
    		}
		}
    	catch( Exception e )
		{
    		e.printStackTrace();
    		throw new ScalarException( "Could not build the Transition List from the Database "+ baseProductName + "  " + e );
		}
    	finally
		{
    		try
			{
    			if( rs != null )
    			{
    				rs.close();
    				rs = null;
    			}
    			if( pstmt != null )
    			{
    				pstmt.close();
    				pstmt = null;
    			}
    			if( conn != null )
    			{
    				conn.close();
    				conn = null;
    			}
			}
    		catch (SQLException e1)
			{
				e1.printStackTrace();
			}
		}
    	return transitionList;
	}
}